package jnpf.onlinedev.util.onlineDevUtil;

import cn.hutool.core.util.ObjectUtil;
import com.alibaba.fastjson.JSONArray;
import jnpf.base.model.template6.ColumnListField;
import jnpf.base.util.VisualUtils;
import jnpf.database.entity.DbLinkEntity;
import jnpf.database.util.DataSourceUtil;
import jnpf.database.util.DbTypeUtil;
import jnpf.model.visiual.JnpfKeyConsts;
import jnpf.model.visiual.TableModel;
import jnpf.onlinedev.model.OnlineDevListModel.OnlineColumnChildFieldModel;
import jnpf.onlinedev.model.OnlineDevListModel.OnlineColumnFieldModel;
import jnpf.onlinedev.model.OnlineDevListModel.OnlineListSqlModel;
import jnpf.onlinedev.model.OnlineDevListModel.VisualColumnSearchVO;
import jnpf.util.JsonUtil;
import jnpf.util.StringUtil;
import jnpf.util.context.SpringContext;
import lombok.Cleanup;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;

/**
 *
 *  生成在线sql语句
 * @author JNPF开发平台组
 * @version V3.2.8
 * @copyright 引迈信息技术有限公司（https://www.jnpfsoft.com）
 * @date  2021/11/8
 */
public class OnlineProductSqlUtils {
	private static DataSourceUtil dataSourceUtil = SpringContext.getBean(DataSourceUtil.class);
	/**
	 * 生成列表查询sql
	 * @param sql
	 * @param tableModelList 表集合
	 * @param modelList
	 * @param linkEntity
	 * @return
	 */
	public static StringBuilder getColumnListSql(StringBuilder sql, List<TableModel> tableModelList, List<ColumnListField> modelList, DbLinkEntity linkEntity) {
		try {
			@Cleanup Connection conn = VisualUtils.getDataConn(linkEntity);
			//主表
			TableModel mainTable = tableModelList.stream().filter(model -> model.getTypeId().equals("1")).findFirst().orElse(null);
			//获取主键
			String pKeyName = VisualUtils.getpKey(conn, mainTable.getTable());
			//列表中区别子表正则
			String reg = "^[jnpf_]\\S*_jnpf\\S*";
			//所有字段
			List<String> collect = modelList.stream().map(mode -> mode.getProp()).collect(Collectors.toList());
			//列表主表字段
			List<String> mainTableFields = collect.stream().filter(s -> !s.matches(reg)).collect(Collectors.toList());
			//主表字段是否包含主键
			if (!mainTableFields.contains(pKeyName.toLowerCase())) {
				mainTableFields.add(pKeyName);
			}

			//列表子表字段
			List<OnlineColumnFieldModel> childFieldList = collect.stream().filter(s -> s.matches(reg)).map(child -> {
				OnlineColumnFieldModel fieldModel = new OnlineColumnFieldModel();
				String s1 = child.substring(child.lastIndexOf("jnpf_")).replace("jnpf_","");
				String s2 = child.substring(child.indexOf("_")+1, child.lastIndexOf("_jnpf"));
						fieldModel.setTableName(s2);
						fieldModel.setField(s1);
						fieldModel.setOriginallyField(child);
				return fieldModel;
			}).collect(Collectors.toList());

			//取列表用到的表
			List<String> ColumnTableNameList = childFieldList.stream().map(t -> t.getTableName()).collect(Collectors.toList());
			List<TableModel> tableModelList1 = tableModelList.stream().filter(t -> ColumnTableNameList.contains(t.getTable())).collect(Collectors.toList());
			List<OnlineColumnChildFieldModel> classifyFieldList = new ArrayList<>(10);
			for (TableModel t : tableModelList1) {
				OnlineColumnChildFieldModel childFieldModel = new OnlineColumnChildFieldModel();
				childFieldModel.setTable(t.getTable());
				childFieldModel.setRelationField(t.getRelationField());
				childFieldModel.setTableField(t.getTableField());
				classifyFieldList.add(childFieldModel);
			}

			String mainSqlFields = mainTableFields.stream().collect(Collectors.joining(","));
			String childFields = "";
			if (classifyFieldList.size() > 0) {
				mainSqlFields = mainTableFields.stream().map(m -> mainTable.getTable() + "." + m).collect(Collectors.joining(","));
				childFields = "," + childFieldList.stream().map(c -> c.getTableName() + "." + c.getField() + " AS " + c.getOriginallyField()).collect(Collectors.joining(","));
			}
			OnlineListSqlModel listSqlModel = new OnlineListSqlModel();
			listSqlModel.setFields(mainSqlFields + childFields);
			listSqlModel.setMainTable(mainTable.getTable());
			listSqlModel.setPKeyName(pKeyName);

			//获取查询语句
			sql = getSqlByDatabase(classifyFieldList, listSqlModel, linkEntity);

		} catch (SQLException sqlException) {
			sqlException.printStackTrace();
		}
		return sql;
	}

	public static StringBuilder getSqlByDatabase(List<OnlineColumnChildFieldModel> classifyFieldList, OnlineListSqlModel listSqlModel, DbLinkEntity linkEntity) {
		StringBuilder sql = new StringBuilder();
		DataSourceUtil dataSourceUtil = SpringContext.getBean(DataSourceUtil.class);
		String mainTable = listSqlModel.getMainTable();

		//金仓 oracle 需关闭大小写敏感
		//数据源
		if (linkEntity != null) {
			if (DbTypeUtil.checkKingbase(linkEntity)) {
				mainTable = linkEntity.getUserName() + "." + listSqlModel.getMainTable();
			}
		}
		//默认数据源
		else {
			if (DbTypeUtil.checkKingbase(dataSourceUtil)) {
				mainTable = linkEntity.getUserName() + "." + listSqlModel.getMainTable();
			}
		}
		{
			if (classifyFieldList.size() > 0) {
				sql.append("select " + listSqlModel.getFields() + " from" + " " + mainTable);
				for (OnlineColumnChildFieldModel model : classifyFieldList) {
					sql.append(" LEFT JOIN " + model.getTable() + " ON " + model.getTable() + "." + model.getTableField() + "=" + mainTable + "." + model.getRelationField() + " ");
				}
			} else {
				sql.append("select " + listSqlModel.getFields() + " from" + " " + mainTable );
			}
		}
		return sql;
	}

	/**
	 * 占位符
	 * @param sql
	 * @param linkEntity
	 * @param searchVOList
	 * @param mainTable
	 * @return
	 */
	public static StringBuilder conditionSql(StringBuilder sql, DbLinkEntity linkEntity, List<VisualColumnSearchVO> searchVOList,String mainTable){
		boolean isOracle = "Oracle".equalsIgnoreCase(linkEntity.getDbType());
		for (int k = 0 ; k < searchVOList.size(); k++){
			VisualColumnSearchVO vo = searchVOList.get(k);
			String jnpfKey = vo.getConfig().getJnpfKey();
			String searchType = vo.getSearchType();
			String vModel = vo.getVModel();
			String format;
			String tableName = StringUtil.isNotEmpty(vo.getConfig().getTableName()) ? vo.getConfig().getTableName() : mainTable;
			Boolean isMultiple = vo.getMultiple()!=null ? vo.getMultiple() : false;
			if (isMultiple ){
				searchType = "2";
			}
			if ("1".equals(searchType)){
				sql.append("AND " + tableName + "." + vModel + "=?");
			}else if ("2".equals(searchType)){
				sql.append("AND " + tableName + "." + vModel + "like ?");
			}else if ("3".equals(searchType)){
				switch (jnpfKey){
					case JnpfKeyConsts.MODIFYTIME:
					case JnpfKeyConsts.CREATETIME:
					case JnpfKeyConsts.DATE:
						format = StringUtil.isEmpty(vo.getFormat()) ? "yyyy-MM-dd HH:mm:ss" : vo.getFormat();
						if (isOracle){
							sql.append("AND " + tableName + "." + vModel + "between to_date( ?" + ",'" +format +"'" +") AND to_date( ? ,'" + format +"')" );
						}else {
							sql.append("AND " + tableName + "." + vModel + "between  ? AND  ? ");
						}
						break;
					case JnpfKeyConsts.TIME:
						 format = "HH:mm:ss";
						if (isOracle){
							sql.append("AND " + tableName + "." + vModel + "between to_date( ?" + ",'" +format +"'" +") AND to_date( ? ,'" + format +"')" );
						}else {
							sql.append("AND " + tableName + "." + vModel + "between  ? AND  ? ");
						}
						break;
					case JnpfKeyConsts.NUM_INPUT:
					case JnpfKeyConsts.CALCULATE:
						List<String> searchArray = (List<String>)vo.getValue();
						Integer firstValue = null;
						Integer secondValue = null;
						for(int i=0;i<searchArray.size();i++){
							String name = searchArray.get(i);
							if(StringUtil.isNotEmpty(name)) {
								if (i == 0) {
									firstValue =Integer.valueOf(name);
								} else {
									secondValue =Integer.valueOf(name);
								}
							}
						}
						if (ObjectUtil.isNotEmpty(firstValue)){
							sql.append("AND " +  tableName + "." + vModel + " >= ? " );
						}
						if (ObjectUtil.isNotEmpty(secondValue)){
							sql.append("AND " +  tableName + "." + vModel + " <= ? " );
						}
						break;
					default:
						break;
				}
			}
		}
		return sql;
	}

	/**
	 *
	 */
	public static StringBuilder getConditionSql(StringBuilder sql, DbLinkEntity linkEntity, List<VisualColumnSearchVO> searchVOList,String mainTable){

		boolean isOracle = DbTypeUtil.checkOracle(dataSourceUtil);
		if (linkEntity != null) {
			isOracle = linkEntity.getDbType().toLowerCase().contains("oracle");
		}

		for (int k = 0 ; k < searchVOList.size(); k++){
			VisualColumnSearchVO vo = searchVOList.get(k);
			String jnpfKey = vo.getConfig().getJnpfKey();
			String searchType = vo.getSearchType();
			String vModel = vo.getVModel();
			String value = String.valueOf(vo.getValue());
			String format;
			String tableName = StringUtil.isNotEmpty(vo.getConfig().getTableName()) ? vo.getConfig().getTableName() : mainTable;
			Boolean isMultiple = vo.getMultiple()!=null ? vo.getMultiple() : false;
			if (isMultiple || JnpfKeyConsts.CHECKBOX.equals(jnpfKey)  || JnpfKeyConsts.CASCADER.equals(jnpfKey)){
				searchType = "2";
			}
			if ("1".equals(searchType)){
				sql.append("AND " + tableName + "." + vModel + " = " + "'"+ value+ "'");
			}else if ("2".equals(searchType)){
				sql.append("AND " + tableName + "." + vModel + " like " +"'%"+value+"%'");
			}else if ("3".equals(searchType)){
				switch (jnpfKey){
					case JnpfKeyConsts.MODIFYTIME:
					case JnpfKeyConsts.CREATETIME:
					case JnpfKeyConsts.DATE:
						JSONArray timeStampArray = (JSONArray) vo.getValue();
						Long o1 =(Long) timeStampArray.get(0);
						Long o2 = (Long) timeStampArray.get(1);
						format = StringUtil.isEmpty(vo.getFormat()) ? "yyyy-MM-dd HH:mm:ss" : vo.getFormat();
						//时间戳转string格式
						String startTime = OnlinePublicUtils.getDateByFormat(o1,format);
						String endTime = OnlinePublicUtils.getDateByFormat(o2,format);
						//处理时间查询条件范围
						endTime=endTime.substring(0,10);
						String firstTimeDate = OnlineDatabaseUtils.getTimeFormat(startTime);
						String lastTimeDate =  OnlineDatabaseUtils.getLastTimeFormat(endTime);

						if (isOracle){
							sql.append("AND " + tableName + "." + vModel + " between to_date("+ "'" +firstTimeDate+ "','yyyy-mm-dd hh24:mi:ss'" +") AND to_date("+ "'" +lastTimeDate+ "','yyyy-mm-dd hh24:mi:ss')" );
						}else {
							sql.append("AND " + tableName + "." + vModel + " between " +"'" +firstTimeDate +"'" + "AND " +"'" +lastTimeDate +"'");
						}
						break;
					case JnpfKeyConsts.TIME:
						List<String> stringList = JsonUtil.getJsonToList(value, String.class);
						format = "HH:mm:ss";
						if (isOracle){
							sql.append("AND " + tableName + "." + vModel + " between to_date("+ "'" +stringList.get(0)+ "','" +format +"'" +") AND to_date("+ "'" +stringList.get(1)+ "','" + format +"')" );
						}else {
							sql.append("AND " + tableName + "." + vModel + " between " +"'" +stringList.get(0) +"'" + "AND " +"'" +stringList.get(1) +"'");
						}
						break;
					case JnpfKeyConsts.NUM_INPUT:
					case JnpfKeyConsts.CALCULATE:
						Float firstValue = null;
						Float secondValue = null;
						JSONArray objects = (JSONArray) vo.getValue();
						for (int i =0;i<objects.size();i++){
							Object n = objects.get(i);
							if (ObjectUtil.isNotEmpty(n)){
								if (i==0){
									firstValue = Float.parseFloat(String.valueOf(n));
								}else {
									secondValue = Float.parseFloat(String.valueOf(n));
								}
							}
						}
						if (ObjectUtil.isNotEmpty(firstValue)){
							sql.append("AND " +  tableName + "." + vModel + " >= " + firstValue);
						}
						if (ObjectUtil.isNotEmpty(secondValue)){
							sql.append("AND " +  tableName + "." + vModel + " <= " + secondValue);
						}
						break;
					default:
						break;
				}
			}
		}
		return sql;
	}
}